"""
Case Type   : 事件触发器
Case Name   : 【事件触发器】创建事件触发器，触发器名称校验
Create At   :   2024/07/15
Owner       :   sungang14
Description :
    预置条件：
        1.创建事件触发器函数：
        CREATE OR REPLACE FUNCTION f_eventtrigger_0049()
        RETURNS event_trigger
        LANGUAGE plpgsql
        AS $$ BEGIN
        RAISE NOTICE 'test_event_trigger: % %', tg_event, tg_tag;
        END;
        $$;
    测试步骤：
        1、事件触发器名称不能包含A-Z,a-z,0-9,_,$,#以外字符，且不以数字/$/#开头
        CREATE EVENT TRIGGER eventtri_eventtrigger_0049 ON ddl_command_start EXECUTE PROCEDURE f_eventtrigger_0049();
        CREATE EVENT TRIGGER eventtri_eventtrigger_0049@1 ON ddl_command_start EXECUTE PROCEDURE f_eventtrigger_0049();
        CREATE EVENT TRIGGER _eventtri_eventtrigger_0049 ON ddl_command_start EXECUTE PROCEDURE f_eventtrigger_0049();
        CREATE EVENT TRIGGER 2eventtri_eventtrigger_0049 ON ddl_command_start EXECUTE PROCEDURE f_eventtrigger_0049();
        CREATE EVENT TRIGGER #eventtri_eventtrigger_0049 ON ddl_command_start EXECUTE PROCEDURE f_eventtrigger_0049();
        CREATE EVENT TRIGGER $eventtri_eventtrigger_0049 ON ddl_command_start EXECUTE PROCEDURE f_eventtrigger_0049();
        2、触发器名称为空
        CREATE EVENT TRIGGER ON ddl_command_start EXECUTE PROCEDURE f_eventtrigger_0049();
        3、触发器名称最大允许长度63个字符
        CREATE EVENT TRIGGER eventtri_eventtrigger_0049_11111111111111111111111111111111test111 ON ddl_command_start EXECUTE PROCEDURE f_eventtrigger_0049();
        CREATE EVENT TRIGGER eventtri_eventtrigger_0049_1111111111111111111111111111testtest ON ddl_command_start EXECUTE PROCEDURE f_eventtrigger_0049();
Expect      : 
        1、创建成功
        创建失败
        创建成功
        创建失败
        创建失败
        创建失败
        2、创建失败
        3、创建成功，事件触发器 名称eventtri_eventtrigger_0049_111111111111111111111111111111111111111111111test;
        创建成功
History     : 
"""

import sys
import os
import unittest
from yat.test import Node
from yat.test import macro

sys.path.append(sys.path[0] + "/../")
from testcase.utils.Logger import Logger
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH


class IndexFileDamaged(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.logger.info('----------------this is setup-----------------------')
        self.DB_ENV_PATH = macro.DB_ENV_PATH
        self.DB_INSTANCE_PATH = macro.DB_INSTANCE_PATH
        self.comsh = CommonSH('PrimaryDbBmode')
        self.comsh.execut_db_sql("drop EVENT TRIGGER if exists eventtri_eventtrigger_0049;")
        self.comsh.execut_db_sql("drop EVENT TRIGGER if exists eventtri_eventtrigger_0049@1;")
        self.comsh.execut_db_sql("drop EVENT TRIGGER if exists _eventtri_eventtrigger_0049;")
        self.comsh.execut_db_sql("drop EVENT TRIGGER if exists 2eventtri_eventtrigger_0049;")
        self.comsh.execut_db_sql("drop EVENT TRIGGER if exists #eventtri_eventtrigger_0049;")
        self.comsh.execut_db_sql("drop EVENT TRIGGER if exists $eventtri_eventtrigger_0049;")
        self.comsh.execut_db_sql(
            "drop EVENT TRIGGER if exists eventtri_eventtrigger_0049_11111111111111111111111111111111test;")
        self.comsh.execut_db_sql(
            "drop EVENT TRIGGER if exists eventtri_eventtrigger_0049_1111111111111111111111111111testtest;")
        self.comsh.execut_db_sql("drop function if exists f_eventtrigger_0049();")
        msg = self.comsh.execut_db_sql(
            f"CREATE OR REPLACE FUNCTION f_eventtrigger_0049() RETURNS event_trigger LANGUAGE plpgsql" \
            f" AS \$\$ BEGIN" \
            f" RAISE NOTICE 'test_event_trigger: % %', tg_event, tg_tag;" \
            f"END;" \
            f"\$\$;")
        self.logger.info(msg)
        self.assertIn("CREATE FUNCTION", msg)

    def test_Index_file_damaged(self):
        self.logger.info("事件触发器名称不能包含A-Z,a-z,0-9,_,$,#以外字符，且不以数字/$/#开头")
        msg = self.comsh.execut_db_sql(
            "CREATE EVENT TRIGGER eventtri_eventtrigger_0049 ON ddl_command_start EXECUTE PROCEDURE f_eventtrigger_0049();")
        self.logger.info(msg)
        self.assertEqual(f'CREATE EVENT TRIGGER', msg)
        msg = self.comsh.execut_db_sql(
            "CREATE EVENT TRIGGER eventtri_eventtrigger_0049@1 ON ddl_command_start EXECUTE PROCEDURE f_eventtrigger_0049();")
        self.logger.info(msg)
        self.assertIn(f'ERROR:  syntax error at or near "@"', msg)
        msg = self.comsh.execut_db_sql(
            "CREATE EVENT TRIGGER _eventtri_eventtrigger_0049 ON ddl_command_start EXECUTE PROCEDURE f_eventtrigger_0049();")
        self.logger.info(msg)
        self.assertEqual(f'CREATE EVENT TRIGGER', msg)
        msg = self.comsh.execut_db_sql(
            "CREATE EVENT TRIGGER 2eventtri_eventtrigger_0049 ON ddl_command_start EXECUTE PROCEDURE f_eventtrigger_0049();")
        self.logger.info(msg)
        self.assertIn(
            f'ERROR:  syntax error at or near "2". Please note that database object name should start with a letter or underscore (_).',
            msg)
        msg = self.comsh.execut_db_sql(
            "CREATE EVENT TRIGGER #eventtri_eventtrigger_0049 ON ddl_command_start EXECUTE PROCEDURE f_eventtrigger_0049();")
        self.logger.info(msg)
        self.assertIn(f'ERROR:  syntax error at end of input', msg)
        msg = self.comsh.execut_db_sql(
            "CREATE EVENT TRIGGER $eventtri_eventtrigger_0049 ON ddl_command_start EXECUTE PROCEDURE f_eventtrigger_0049();")
        self.logger.info(msg)
        self.assertIn(f'ERROR:  syntax error at or near "ON"', msg)

        self.logger.info("事件触发器名称为空")
        msg = self.comsh.execut_db_sql(
            "CREATE EVENT TRIGGER ON ddl_command_start EXECUTE PROCEDURE f_eventtrigger_0049();")
        self.logger.info(msg)
        self.assertIn(f'ERROR:  syntax error at or near "ON"', msg)

        self.logger.info("触发器名称最大允许长度63个字符")
        msg = self.comsh.execut_db_sql(
            "CREATE EVENT TRIGGER eventtri_eventtrigger_0049_11111111111111111111111111111111test111 ON ddl_command_start EXECUTE PROCEDURE f_eventtrigger_0049();")
        self.logger.info(msg)
        self.assertIn(
            f'NOTICE:  identifier "eventtri_eventtrigger_0049_11111111111111111111111111111111test111" will be truncated to "eventtri_eventtrigger_0049_11111111111111111111111111111111test"' \
            and f'CREATE EVENT TRIGGER',
            msg)
        msg = self.comsh.execut_db_sql(
            "CREATE EVENT TRIGGER eventtri_eventtrigger_0049_1111111111111111111111111111testtest ON ddl_command_start EXECUTE PROCEDURE f_eventtrigger_0049();")
        self.logger.info(msg)
        self.assertEqual(f'CREATE EVENT TRIGGER', msg)

    def tearDown(self):
        self.logger.info('----------------this is tearDown-----------------------')
        # 删除事件触发器
        msg = self.comsh.execut_db_sql("drop EVENT TRIGGER if exists eventtri_eventtrigger_0049;")
        self.logger.info(msg)
        self.assertEqual('DROP EVENT TRIGGER', msg)
        msg = self.comsh.execut_db_sql("drop EVENT TRIGGER if exists _eventtri_eventtrigger_0049;")
        self.logger.info(msg)
        self.assertEqual('DROP EVENT TRIGGER', msg)
        msg = self.comsh.execut_db_sql(
            "drop EVENT TRIGGER if exists eventtri_eventtrigger_0049_11111111111111111111111111111111test;")
        self.logger.info(msg)
        self.assertIn('DROP EVENT TRIGGER', msg)
        msg = self.comsh.execut_db_sql(
            "drop EVENT TRIGGER if exists eventtri_eventtrigger_0049_1111111111111111111111111111testtest;")
        self.logger.info(msg)
        self.assertIn('DROP EVENT TRIGGER', msg)
        # 删除事件触发器函数
        msg = self.comsh.execut_db_sql("drop function if exists f_eventtrigger_0049();")
        self.logger.info(msg)
        self.assertIn('DROP FUNCTION', msg)
        self.logger.info(f'-----{os.path.basename(__file__)} end-----')
